package dbAdapter;



import CommonClass.IMG;
import CommonClass.keyword;
import CommonClass.URLlink;
import CommonClass.URLSearch;
























import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Queue;
import java.util.ArrayList;
import java.util.Set;
import java.util.Vector;
import java.sql.*;

import javax.swing.text.html.HTMLDocument.Iterator;

public class DatabaseAdapter {


	private static DatabaseAdapter instance = null;



	private String DATABASE_NAME 			= "imagesearch";
	private String KEYWORD_TABLE	 		= "keyword";
	private String RESULT_TABLE	     		= "result";
	private String TEMP_RESULT_TABLE		= "tempresult";
	private String TEMP_KEYWORD_TABLE 		= "tempkeyword";
	private String SEARCHED_TABLE      		= "searchhistory";
	private String KEYWORD_STATISTICS_TABLE	= "keywordstatistics";
	private String TEMP_SEARCH_TABLE 		= "tempsearchlist";
	private String UNPROCESSED_TABLE		= "unprocessed_searchlist";
	private String DATABASE_USERNAME 		= "root";
	private String DATABASE_PASSWORD 		= "1234";

	private	String KEYWORD					="";
	private	boolean isOccupied				= false;


	private Connection connect				= null;
	private Statement  stmt 				= null;
	private HashSet<String> track_unprocessed_hashkey;
	private HashSet<String> temp_result_hashlist;
	private HashSet<String> temp_keyword_hashlist;
	
	

	public DatabaseAdapter() {

		try {
			connectToDatabase();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println(e.getMessage());
		}

	}


	public static DatabaseAdapter getInstance() {

		if(instance == null) {
			instance = new DatabaseAdapter();
		}

		return instance;

	}

	public synchronized boolean getMutex(){

		if(isOccupied == true){
			return false;
		}
		else{
			isOccupied = true;
			return true;
		}

	}

	public synchronized boolean returnMutex(){

		if(isOccupied == true){
			isOccupied = false;
			return true;
		}
		else{
			return false;

		}
	}

	public synchronized void clearSearch(){

		KEYWORD="";
		clearUnprocessedURLList();
	//	clearTempSearchTable();
		track_unprocessed_hashkey.clear();
		temp_result_hashlist.clear();
		 temp_keyword_hashlist.clear();
		return;
	}

	public synchronized void setNewSearch(String _keyword, URLlink firstURL){

		KEYWORD = _keyword;
		clearUnprocessedURLList();
		//clearTempSearchTable();
		addToSearchedList(firstURL);
		addKeywordStatistics(_keyword);
		track_unprocessed_hashkey= new HashSet<String>();
		temp_result_hashlist= new HashSet<String>();
		 temp_keyword_hashlist= new HashSet<String>();
		return;

	}

	public synchronized void setNewSearch(keyword _keyword, URLlink firstURL){


		KEYWORD = _keyword.getKeyword();
		clearUnprocessedURLList();
		//clearTempSearchTable();
		addToSearchedList(firstURL);
		addKeywordStatistics(_keyword.getURL());
		track_unprocessed_hashkey= new HashSet<String>();
		temp_result_hashlist= new HashSet<String>();
		 temp_keyword_hashlist= new HashSet<String>();
		return ;
	}

	public synchronized void addNewUnprocessedUrlList(HashMap<URLlink,Integer> newList){
		
		track_unprocessed_hashkey= new HashSet<String>();
		
		java.util.Iterator<Entry<URLlink, Integer>> i = newList.entrySet().iterator(); 
		int j = 0;
		String insertQuery ="insert into " + UNPROCESSED_TABLE +" values";
		String query;
		ResultSet result;
		while(i.hasNext()){

			Map.Entry entry = (Map.Entry) i.next(); 
			URLlink tmplink = (URLlink) entry.getKey();
			int tmpProbability = ((Integer) entry.getValue()).intValue();
			
			if(tmplink.getURL().length()<=250){
				
				query = "select * from " + SEARCHED_TABLE +","+ UNPROCESSED_TABLE + 
						" where "+ SEARCHED_TABLE + ".URL=\"" + tmplink.getURL() + "\" OR " + 
				UNPROCESSED_TABLE + ".URL=\""+ tmplink.getURL()+"\"";
				try{
					
					result = executeSelectQuery(query);
					if(!result.next()){
						
						if(track_unprocessed_hashkey.add(tmplink.getURL().toLowerCase())){
							
							if(j==0){
								
								insertQuery+="(\""+tmplink.getURL()+"\","+tmpProbability+")";
							}else{
							
								insertQuery+=",(\""+tmplink.getURL()+"\","+tmpProbability+")";
							}
							j++;
							
						}
						
					}
				}catch(SQLException e){
					return;
				}
				
			}	
		}
		
		try{
			if(j>0){
			executeUpdateQuery(insertQuery);
			}
		}catch(SQLException e){
		}

		

		return;


	}

	public synchronized void addNewUnprocessedURL(URLlink URL,int probability){


		String query = "select * from " + SEARCHED_TABLE +","+ UNPROCESSED_TABLE + 
						" where "+ SEARCHED_TABLE + ".URL=\"" + URL.getURL() + "\" OR " + 
				UNPROCESSED_TABLE + ".URL=\""+ URL.getURL()+"\"";

		ResultSet result;
		if(URL.getURL().length()<=250){
			try {
				result = executeSelectQuery(query);
				if(result == null){
					addToUnprocessedTable(URL.getURL(),probability);

				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block

				e.printStackTrace();
				return;
			}
		}
		

		return;
	}

	private void insertIntoTempTable(URLlink URL, int probability){

		String query = "insert into " + TEMP_SEARCH_TABLE +" values(\""+URL.getURL()+ "\","+probability+")";
		ResultSet result;
		if(URL.getURL().length()>250){
			return;
		}
		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return;
		}
	}

	private void addToSearchedList(URLlink URL){

		String query = "select * from "+ SEARCHED_TABLE+ " where " + SEARCHED_TABLE + ".URL=\""+URL.getURL()+"\"" ;
		ResultSet result;
		try {
			result = executeSelectQuery(query);

			if(!result.next() ){
				query = "insert into " + SEARCHED_TABLE +" values(\""+URL.getURL()+ "\",NOW(),1)";

			}
			else{
				query = "update "+SEARCHED_TABLE+ " set " + SEARCHED_TABLE+".searchedCount =" +
						SEARCHED_TABLE+".searchedCount + 1 where " +SEARCHED_TABLE+".URL=\""+URL.getURL()+"\"";
			}

			executeUpdateQuery(query);


		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return;
		}


		return;

	}

//	private synchronized String addToUnprocessedTable(String URL, int probability){
//
//		String query = " (\""+URL+"\","+probability+")";
//		ResultSet result;
//		if(URL.length()>250){
//			return "";
//		}
//
//		return  query;
//
//	}

	private synchronized void addToUnprocessedTable(String URL, int probability){

		String query = "insert into " + UNPROCESSED_TABLE +" values(\""+URL+"\","+probability+")";
		if(URL.length()>250){
			return;
		}
		ResultSet result;
		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return;
		}
		return;

	}

	public synchronized int getNumberOfUnprocessedURL(){

		int size = 0;

		String query = "select count(*) from " + UNPROCESSED_TABLE;
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			if(result.next()){
				size = result.getInt(1);
			}


		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return 0;
		}

		return size;
	}

	public synchronized URLlink getUnprocessedURL(){

		String query = "select max("+UNPROCESSED_TABLE+".probability) from " + UNPROCESSED_TABLE;
		String URL = null;
		ResultSet result;
		URLlink tempURL = null;;
		int highest_probability = 0;

		try {
			result = executeSelectQuery(query);

			if(result != null){

				if(result.next()){
					highest_probability = result.getInt(1);
				}

				query = "select * from "+UNPROCESSED_TABLE+
						" where "+UNPROCESSED_TABLE+".probability="+highest_probability+" limit 1";

				result = executeSelectQuery(query);
				if(result!=null){
					if(result.next()){
						URL = result.getString("URL");
						tempURL = new URLlink(URL);
					}
				}
				else{
					return null;
				}

			}
			else{
				return null;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}	
		query = "delete from "+ UNPROCESSED_TABLE+ " where URL=\""+ URL+"\"";
		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}	
		addToSearchedList(tempURL);


		return tempURL;

	}

	private void clearUnprocessedURLList(){

		String query = "delete from " + UNPROCESSED_TABLE;
		ResultSet result;
		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return;
		}

		return;

	}

//	private void clearTempSearchTable(){
//
//		ResultSet result;
//		String query = "delete from " + TEMP_SEARCH_TABLE;
//		try {
//			executeUpdateQuery(query);
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//			return;
//		}
//		return;
//
//	}

	private void clearTempResultTable(){

		ResultSet result;
		String query = "delete from " + TEMP_RESULT_TABLE;
		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return;
		}
		return;

	}

	private void clearTempKeywordTable(){

		ResultSet result;
		String query = "delete from " + TEMP_KEYWORD_TABLE;
		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return;
		}
		return;

	}

	public synchronized void addResultList(Vector<String> URL, Vector<IMG> image, Vector<keyword> _keyword){

		temp_result_hashlist= new HashSet<String>();
		temp_keyword_hashlist= new HashSet<String>();
		String insertResultStmt = "insert into " + RESULT_TABLE +" values";
		String insertKeywordStmt = "insert into "+ KEYWORD_TABLE +" values";
		String resultQuery, keywordQuery, tmpSelectQuery,temp;
		int UrlSize= URL.size();
		int imageSize = image.size();
		int keywordSize = _keyword.size();
		ResultSet keywordResult;
		ResultSet imageResult;
		ResultSet tempResult;
		int resultCount = 0, keywordCount =0;
		if(UrlSize == imageSize && UrlSize == keywordSize){

			for(int i =0;i< UrlSize;i++){

				if(image.get(i).getImageURLlink().length() <=250 && URL.get(i).length()<250){
					
					resultQuery = "select * from "+ RESULT_TABLE +
								  " where "+RESULT_TABLE+".URL=\""+ URL.get(i)+"\" AND "+
									RESULT_TABLE+".imageURL=\""+image.get(i).getImageURLlink()+"\"";
					
					try {
						imageResult = executeSelectQuery(resultQuery);
						
						if(!imageResult.next()){
							
							temp = appendTwoString(URL.get(i),image.get(i).getImageURLlink());
							if(temp_result_hashlist.add(temp)){
								
								if(resultCount==0){
									insertResultStmt+="(\""+URL.get(i)+"\",\""+image.get(i).getImageURLlink()+"\","+image.get(i).getHeight()+","+image.get(i).getWidth()+")";
								}else{
									insertResultStmt+=",(\""+URL.get(i)+"\",\""+image.get(i).getImageURLlink()+"\","+image.get(i).getHeight()+","+image.get(i).getWidth()+")";
								}
								resultCount++;
							}
							
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}

				if(_keyword.get(i).getKeyword().length()<=250 && image.get(i).getImageURLlink().length()<=250 && URL.get(i).length()<=250){
					
					keywordQuery =  "select * from "+ KEYWORD_TABLE +
									" where "+KEYWORD_TABLE+".URL=\""+ URL.get(i)+"\" AND "+
									KEYWORD_TABLE+".imageURL=\""+image.get(i).getImageURLlink()+"\" AND "+
									KEYWORD_TABLE+".keyword=\""+_keyword.get(i).getKeyword()+"\"";
					
					try {
						keywordResult = executeSelectQuery(keywordQuery);
						
						if(!keywordResult.next()){
							
							temp=appendThreeString(_keyword.get(i).getKeyword(),image.get(i).getImageURLlink(),URL.get(i));
							if(temp_keyword_hashlist.add(temp)){
								
								if(keywordCount==0){
									insertKeywordStmt+="(\""+_keyword.get(i).getKeyword()+"\",\""+image.get(i).getImageURLlink()+"\",\""+URL.get(i)+"\",\""+_keyword.get(i).getTag()+"\")";
								}else{
									insertKeywordStmt+=",(\""+_keyword.get(i).getKeyword()+"\",\""+image.get(i).getImageURLlink()+"\",\""+URL.get(i)+"\",\""+_keyword.get(i).getTag()+"\")";
								}
								keywordCount++;
							}
						}
						
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				}
			}

			
			if(resultCount > 0){
				try {
					executeUpdateQuery(insertResultStmt);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}else
			{
				System.out.println("Nothing added to Result table");
			}
			if(keywordCount > 0){
				try {
					executeUpdateQuery(insertKeywordStmt);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}else{
				System.out.println("Nothing added to keyword table");
			}
			


		}
		else{
			System.out.println("Results are not added to the database as the vector sizes are different");
			return;
		}
	}

	public synchronized void addResult(String URL, IMG image, keyword _keyword){

		String query = "insert into " + RESULT_TABLE +
				" values(\""+URL+"\",\""+image.getImageURLlink()+"\",0,0)";
		ResultSet result;
		if(URL.length()>250 || image.getImageURLlink().length()>250 || _keyword.getKeyword().length()>250){
			return;
		}
		try {
			executeUpdateQuery(query);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return;
		}

		query = "insert into " + KEYWORD_TABLE +
				" values(\""+_keyword.getKeyword()+"\",\""+image.getImageURLlink()+"\",\""+URL+"\",\""+_keyword.getTag()+"\")";

		try {
			executeUpdateQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return;
		}

	}

	public synchronized void addKeywordStatistics(String _keyword){
		
		String query = "select * from " + KEYWORD_STATISTICS_TABLE +
						" where "+ KEYWORD_STATISTICS_TABLE + ".keyword=\"" +_keyword +"\"";
		
		ResultSet result;
		try {
			result = executeSelectQuery(query);

			if(!result.next() ){
				query = "insert into " + KEYWORD_STATISTICS_TABLE +" values(\""+_keyword+ "\",1)";

			}
			else{
				query = "update "+KEYWORD_STATISTICS_TABLE+ " set " + 
						 KEYWORD_STATISTICS_TABLE+".searchedCount + 1 where "
						+KEYWORD_STATISTICS_TABLE+".keyword=\""+_keyword+"\"";
			}

			executeUpdateQuery(query);


		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return;
		}


		return;
						
		
		
	}
	public Vector<IMG> getImageFromKeyword(String _keyword) {

		int size =0;
		Vector<IMG> tempImageURLList = new Vector<IMG>(); 
		String query = "select * from " + KEYWORD_TABLE +","+ RESULT_TABLE + 
				" where "+ KEYWORD_TABLE + ".URL=" + RESULT_TABLE +".URL AND "+
				KEYWORD_TABLE + ".imageURL=" + RESULT_TABLE +".imageURL AND "+ KEYWORD_TABLE+
				".keyword=\"" +_keyword +"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				IMG temp = new IMG(result.getString("imageURL"),
						result.getInt("height"),
						result.getInt("width"));

				tempImageURLList.add(temp);

				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		System.out.println(size);
		return tempImageURLList;
	}

	public Vector<IMG> getImageFromURL(URLlink URL){

		int size =0;
		Vector<IMG> tempImageURLList = new Vector<IMG>(); 
		String query = "select * from " + KEYWORD_TABLE +","+ RESULT_TABLE + 
				" where "+ KEYWORD_TABLE + ".URL=" + RESULT_TABLE +".URL AND "+
				KEYWORD_TABLE + ".imageURL=" + RESULT_TABLE +".imageURL AND "+ 
				KEYWORD_TABLE + ".URL=\"" + URL.getURL() +"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				IMG temp = new IMG(result.getString("imageURL"),
						result.getInt("height"),
						result.getInt("width"));

				tempImageURLList.add(temp);

				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


		return tempImageURLList;

	}

	public Vector<keyword> getKeywordFromURL(URLlink url){

		int size =0;
		Vector<keyword> tempKeywordList = new Vector<keyword>();
		String query = "select * from " + KEYWORD_TABLE + " where URL=\"" + url.getURL() +"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				keyword temp = new keyword(result.getString("tag"),
						result.getString("keyword") );

				tempKeywordList.add(temp);

				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		System.out.println(size);


		return tempKeywordList;
	}

	public Vector<keyword> getKeywordFromImage(IMG image){

		int size = 0;
		Vector<keyword> tempKeywordList = new Vector<keyword>();
		String query = "select * from " + KEYWORD_TABLE + " where imageURL=\"" + image.getImageURLlink()+"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				keyword temp = new keyword(result.getString("tag"),
						result.getString("keyword"));

				tempKeywordList.add(temp);

				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}




		return tempKeywordList;

	}

	public Vector<URLlink> getURLlinkFromImageURL(IMG imageURL){

		int size =0;
		Vector<URLlink> tempURLlinkList = new Vector<URLlink>();
		String query = "select * from " + RESULT_TABLE + " where imageURL=\"" + imageURL.getImageURLlink() +"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				URLlink temp = new URLlink(result.getString("URL"));

				tempURLlinkList.add(temp);

				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}




		return tempURLlinkList;

	}

	public Vector<URLlink> getURLlinkFromKeyword(String _keyword){

		int size =0;
		Vector<URLlink> tempURLlinkList = new Vector<URLlink>();
		String query = "select * from " + KEYWORD_TABLE + " where keyword=\"" + _keyword +"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				URLlink temp = new URLlink(result.getString("URL"));

				tempURLlinkList.add(temp);

				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}



		return tempURLlinkList;

	}

	public Vector<IMG> getImageFromSimilarKeyword(String keyword){

		Vector<IMG> tempImageURLList = new Vector<IMG>(); 
		String query = "select * from " + KEYWORD_TABLE +","+ RESULT_TABLE + 
				" where "+ KEYWORD_TABLE + ".URL=" + RESULT_TABLE +".URL AND "+
				KEYWORD_TABLE + ".imageURL=" + RESULT_TABLE +".imageURL AND "+ KEYWORD_TABLE +
				".keyword LIKE \"%" + keyword +"%\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				IMG temp = new IMG(result.getString("imageURL"),
						result.getInt("height"),
						result.getInt("width"));

				tempImageURLList.add(temp);


			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

		return tempImageURLList;


	}

	public Vector<URLlink> getURLlinkFromSimilarKeyword(String keyword){

		int size =0;
		Vector<URLlink> tempURLlinkList = new Vector<URLlink>();
		String query = "select * from " + KEYWORD_TABLE + " where keyword LIKE \"%" + keyword +"%\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				URLlink temp = new URLlink(result.getString("URL"));

				tempURLlinkList.add(temp);

				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}




		return tempURLlinkList;


	}

	public Vector<IMG> getIMGFromMatchingUrlAndKeyword(String _keyword, String urlLink){

		Vector<IMG> tempImageURLList = new Vector<IMG>(); 
		String query = "select * from " + KEYWORD_TABLE +","+ RESULT_TABLE + 
				" where "+ KEYWORD_TABLE + ".URL=" + RESULT_TABLE +".URL AND "+
				KEYWORD_TABLE + ".imageURL=" + RESULT_TABLE +".imageURL AND "+
				RESULT_TABLE +".URL=\""+urlLink+"\" AND "+
				KEYWORD_TABLE +".keyword LIKE \"%" + _keyword +"%\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				IMG temp = new IMG(result.getString("imageURL"),
						result.getInt("height"),
						result.getInt("width"));

				tempImageURLList.add(temp);


			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

		return tempImageURLList;

	}

	public Vector<URLlink> getTopTenMostSearchedhURL(){
	
		
		
		Vector<URLlink> tempURLlinkList = new Vector<URLlink>();
		String query = "select * from " + SEARCHED_TABLE + " order by searchedCount desc limit 10";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				URLlink temp = new URLlink(result.getString("URL"));

				tempURLlinkList.add(temp);

				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

		return tempURLlinkList;
		
	}
	
	public Vector<URLlink> getNumberOneMostSearchURL(){
		
		Vector<URLlink> tempURLlinkList = new Vector<URLlink>();
		String query = "select * from " + SEARCHED_TABLE + " where " + SEARCHED_TABLE+".searchedCount=("+
						"select max(searchedCount) FROM "+SEARCHED_TABLE+")";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				URLlink temp = new URLlink(result.getString("URL"));

				tempURLlinkList.add(temp);

				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

		return tempURLlinkList;
	}
	
	public Vector<String> getTopTenMostSearchedKeyword(){
		
		
		Vector<String> tempKeywordList = new Vector<String>();
		String query = "select * from " + KEYWORD_STATISTICS_TABLE + " ORDER BY searchCount DESC LIMIT 10";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				tempKeywordList.add(result.getString("keyword"));

				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		

		return tempKeywordList;
		
		
	}
	
	public Vector<String> getNumberOneMostSearchedKeyword(){
		
		
		Vector<String> tempKeywordList = new Vector<String>();
		String query = "select * from " + KEYWORD_STATISTICS_TABLE + 
						" WHERE searchCount = (select max(searchCount) from "+ KEYWORD_STATISTICS_TABLE +")";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){

				tempKeywordList.add(result.getString("keyword"));

				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		

		return tempKeywordList;
		
		
	}
	
	public int getSearchedCountOfKeyword(String keyword){
		
		int size = 0;

		String query = "select searchCount from " + KEYWORD_STATISTICS_TABLE + 
						" where " + KEYWORD_STATISTICS_TABLE+".keyword=\""+keyword+"\"";
		ResultSet result; 
		try {
			result = executeSelectQuery(query);
			if(result.next()){
				size = result.getInt(1);
			}


		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return 0;
		}

		return size;
		
	}
	
	public int getNumberOfJPEGImageFile(){
		
		int size = 0;

		String query = "select count(distinct(imageURL)) from " + RESULT_TABLE + 
						" where " + RESULT_TABLE+".imageURL LIKE \"%.jpg%\"";
		ResultSet result; 
		try {
			result = executeSelectQuery(query);
			if(result.next()){
				size = result.getInt(1);
			}


		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return 0;
		}

		return size;
		
		
	}
	
	public int getNumberOfPNGImageFile(){
		
		
		int size = 0;

		String query = "select count(distinct(imageURL)) from " + RESULT_TABLE + 
						" where " + RESULT_TABLE+".imageURL LIKE \"%.png%\"";
		ResultSet result; 
		try {
			result = executeSelectQuery(query);
			if(result.next()){
				size = result.getInt(1);
			}


		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return 0;
		}

		return size;
		
	}
	
	public int getNumberOfGifImageFile(){
		
		int size = 0;

		String query = "select count(distinct(imageURL)) from " + RESULT_TABLE + 
						" where " + RESULT_TABLE+".imageURL LIKE \"%.gif%\"";
		ResultSet result; 
		try {
			result = executeSelectQuery(query);
			if(result.next()){
				size = result.getInt(1);
			}


		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return 0;
		}

		return size;
		
		
	}
	
	public int getNumberOfBitmapImagefile(){
		
		
		int size = 0;

		String query = "select count(distinct(imageURL)) from " + RESULT_TABLE + 
						" where " + RESULT_TABLE+".imageURL= LIKE \"%.bmp%\"";
		ResultSet result; 
		try {
			result = executeSelectQuery(query);
			if(result.next()){
				size = result.getInt(1);
			}


		} catch (SQLException e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
			return 0;
		}

		return size;
		
	}
	
	public boolean isKeywordExisted(keyword _keyword){

		String query = "select * from " + KEYWORD_TABLE + " where keyword=\"" + _keyword.getKeyword() +"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			if(!result.next())
			{
				return false;
			}
			else
			{
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

	}

	public boolean isKeywordExisted(String keyword){

		String query = "select * from " + KEYWORD_TABLE + " where keyword=\"" + keyword +"\"";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			if(!result.next())
			{
				return false;
			}
			else
			{
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	private void connectToDatabase() throws SQLException {


		try {
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("Connected to database");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("Unable to connect to database connection");
		} // Setup the connection with the DB

		try {
			connect = DriverManager.getConnection("jdbc:mysql://localhost/"+DATABASE_NAME,
					DATABASE_USERNAME,
					DATABASE_PASSWORD);


			System.out.println("Connected to database connection");


		} catch (SQLException e) {

			System.out.println("Unable to connect to database");
		}

	}

	private ResultSet executeSelectQuery(String query) throws SQLException{

		try {
			stmt = connect.createStatement();
			ResultSet result = stmt.executeQuery(query);
			System.out.println("Select Query is successful");

			return result;


		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("Select Query is unsuccessful");
			System.out.println(e.getMessage());
			return null;
		}

	}

	private void executeUpdateQuery(String query) throws SQLException {

		try{

			stmt = connect.createStatement();
			int i = stmt.executeUpdate(query);
			System.out.println("Update Query is successful");
			System.out.println("Row modified: "+ i);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("Update Query is unsuccessful");
			System.out.println(e.getMessage());
			return;
		}

	}

	private int getQueryResultSize(ResultSet result) throws SQLException{

		int size = 0;

		if (result != null)   
		{  
			result.beforeFirst();  
			result.last();  
			size = result.getRow();  
		}  
		return size;
	}
	
	public String appendThreeString(String var1,String var2, String var3) {
		 
	    String varstr = var1 + var2 + var3;
	    
	  
	  return varstr;
	}
	
	public String appendTwoString(String var1,String var2) {
		 
	    String varstr = var1 + var2;
	    
	  
	  return varstr;
	}

	
	
}
